package cn.Product.dao.impl;

import cn.Product.dao.ProductDao;
import common.service.MryeDataCenterService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;


import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component("productDao")
public class ProductDaoImpl implements ProductDao {

    @Autowired
    MryeDataCenterService mryeDataCenterService;


    /**
     * 获取列表商品
     *
     * @param params
     * @return
     */
    public List<Map<String, Object>> getProductList(Map<String, Object> params) {
        String sql = "SELECT G3.GOODS_ID AS SPID, G3.GOODS_BH AS SPBH, G3.SHOP_ID AS DPID, G3.GOODS_NAME AS SPMC, G3.GOODS_IMG AS SPTP, G3.GOODS_PRICE AS SPDJ, " +
                "G3.GOODS_WEIGHT AS SPZL, G3.GOODS_PRICE_TYPE AS SPDW, G3.GOODS_NUMBER AS SPCH, G3.GOODS_NUMBER_SALE AS SPXS, G3.GOODS_KEYWORD AS SPGJC, G3.GOODS_DES AS SPSM, " +
                "G3.GOODS_CREATE_TIME AS SPTIME, G3.GOODS_ADDRESS AS SPCD, G3.GOODS_ARRAY_ID AS SPDPID,G3.GOODS_SHOW AS SPSHOW, SJ.SJ_ID AS SJLXID, SJ.SJTYPE_NAME AS SJLXMC, SJ.SJTYPE_URL AS SJLXURL, " +
                "EJ.EJ_ID AS EJLXID, EJ.EJTYPE_NAME AS EJLXMC, EJ.EJTYPE_URL AS EJLXURL, YJ.YJ_ID AS YJLXID, YJ.YJTYPE_NAME AS YJLXMC, YJ.YJTYPE_URL AS YJLXURL FROM YST_GOODS_SJTYPE AS SJ " +
                "LEFT JOIN YST_GOODS_EJTYPE EJ ON SJ.EJTYPE_ID = EJ.EJ_ID LEFT JOIN YST_GOODS_YJTYPE YJ ON EJ.YJTYPE_ID = YJ.YJ_ID " +
                "LEFT JOIN ( SELECT G0.* FROM YST_GOODS AS G0 LEFT JOIN YST_GOODS AS G1 ON G0.TYPE_ID = G1.TYPE_ID AND G0.GOODS_ID <= G1.GOODS_ID WHERE G0.GOODS_SHOW=1 GROUP BY G0.GOODS_ID HAVING count(1) <= 20 ) AS G3 ON SJ.SJ_ID = G3.TYPE_ID " +
                "WHERE G3.GOODS_ID <> '' ORDER BY G3.GOODS_ID DESC";
        params.put("sql", sql);
        return mryeDataCenterService.searchSapList(params);
    }

    /**
     * 获取admin维护列表商品
     *
     * @param params
     * @return
     */
    public List<Map<String, Object>> getadminProductList(Map<String, Object> params) {
        String sql = "SELECT G1.GOODS_ID AS SPID, G1.GOODS_BH AS SPBH, G1.TYPE_ID AS LXID, G2.SJTYPE_NAME AS LXMC, G1.SHOP_ID AS DPID, G1.GOODS_NAME AS SPMC, G1.GOODS_IMG AS SPTP, G1.GOODS_PRICE AS SPDJ,G1.GOODS_WEIGHT AS SPZL,G1.GOODS_PRICE_TYPE AS SPDW," +
                " G1.GOODS_NUMBER AS SPCH, G1.GOODS_NUMBER_SALE AS SPXS, G1.GOODS_KEYWORD AS SPGJC, G1.GOODS_DES AS SPSM, G1.GOODS_CREATE_TIME AS SPTIME, G1.GOODS_ADDRESS AS SPCD, G1.GOODS_ARRAY_ID AS SPDPID,G1.GOODS_SHOW AS SPSHOW FROM YST_GOODS G1 " +
                "LEFT JOIN YST_GOODS_SJTYPE G2 ON G1.TYPE_ID = G2.SJ_ID WHERE 1=1 ";
        //根据商品名称筛选
        if (params.containsKey("SPMC") && !params.get("SPMC").equals("")) {
            sql = sql + "AND G1.GOODS_NAME LIKE concat('%',#{SPMC},'%') ";
        }
        //根据时间筛选
        if (params.containsKey("starttime") && !params.get("starttime").equals("")) {
            sql = sql + "AND G1.GOODS_CREATE_TIME > #{starttime}  ";

        }
        if (params.containsKey("endtime") && !params.get("endtime").equals("")) {
            sql = sql + "AND G1.GOODS_CREATE_TIME < #{endtime}   ";

        }
        //根据商品类型筛选
        if (params.containsKey("SPLX") && !params.get("SPLX").equals("")) {
            sql = sql + "AND G1.TYPE_ID = #{SPLX} ";
        }
        sql = sql + "ORDER BY G1.GOODS_ID DESC ";
        //默认查询所有
        if (Integer.parseInt(params.get("start").toString()) >= 0) {
            sql = sql + " LIMIT #{start}, #{end}";
        }
        params.put("sql", sql);

        return mryeDataCenterService.searchSapList(params);
    }

    /**
     * 获取主页随机商品
     *
     * @param
     * @return
     */
    public List<Map<String, Object>> getProductRandom(Map<String, Object> params) {
        String sql = "SELECT G3.GOODS_ID AS SPID, G3.GOODS_BH AS SPBH, G3.TYPE_ID AS LXID, G4.TYPE_NAME AS LXMC, G3.SHOP_ID AS DPID, G3.GOODS_NAME AS SPMC, G3.GOODS_IMG AS SPTP, G3.GOODS_PRICE AS SPDJ, G3.GOODS_WEIGHT AS SPZL, G3.GOODS_PRICE_TYPE AS SPDW," +
                " G3.GOODS_NUMBER AS SPCH, G3.GOODS_NUMBER_SALE AS SPXS, G3.GOODS_KEYWORD AS SPGJC, G3.GOODS_DES AS SPSM,G3.GOODS_CREATE_TIME AS SPTIME, G3.GOODS_ADDRESS AS SPCD, G3.GOODS_ARRAY_ID AS SPDPID,G3.GOODS_SHOW AS SPSHOW FROM YST_GOODS_TYPE AS G4 " +
                "LEFT JOIN ( SELECT G0.* FROM YST_GOODS AS G0 LEFT JOIN YST_GOODS AS G1 ON G0.TYPE_ID = G1.TYPE_ID AND G0.GOODS_ID <= G1.GOODS_ID WHERE G0.GOODS_SHOW=1 GROUP BY G0.GOODS_ID HAVING count(1) <= 5 ) AS G3 ON G4.ID = G3.TYPE_ID " +
                "ORDER BY G3.GOODS_ID DESC";
        params.put("sql", sql);
        return mryeDataCenterService.searchSapList(params);
    }

    /**
     * 获取商品类别
     *
     * @param params
     * @return
     */
    public  Map<String, Object>  getProductType(Map<String, Object> params) {
        Map<String, Object>  map=new HashMap<>();
        String sql = "SELECT  YJ_ID AS YJLXID, " +
                "   YJID_SX AS YJSXID,YJTYPE_BH AS YJLXBH,YJTYPE_NAME AS YJLXMC, " +
                "   YJTYPE_URL AS YJLXURL,YJTYPE_CREATE_TIME AS YJCJSJ FROM YST_GOODS_YJTYPE ORDER BY YJID_SX";
        params.put("sql", sql);
        map.put("YJLX",mryeDataCenterService.searchSapList(params));
        sql = "SELECT  EJ_ID AS EJLXID, " +
                "  EJID_SX AS EJSXID,EJTYPE_BH AS EJLXBH,EJTYPE_NAME AS EJLXMC, " +
                "  EJTYPE_URL AS EJLXURL,YJTYPE_ID  AS YJLXID,EJTYPE_CREATE_TIME AS EJCJSJ FROM YST_GOODS_EJTYPE ORDER BY EJID_SX";
        params.put("sql", sql);
        map.put("EJLX",mryeDataCenterService.searchSapList(params));
        sql = "   SELECT G1.SJ_ID AS SJLXID, G1.SJID_SX AS SJSXID, G1.SJTYPE_BH AS SJLXBH, G1.SJTYPE_NAME AS SJLXMC, G1.SJTYPE_URL AS SJLXURL," +
                " G2.YJTYPE_ID AS YJLXID, G1.EJTYPE_ID AS EJLXID, G1.SJTYPE_CREATE_TIME AS SJCJSJ FROM YST_GOODS_SJTYPE G1 " +
                " LEFT JOIN YST_GOODS_EJTYPE G2 ON G1.EJTYPE_ID = G2.EJ_ID ORDER BY SJID_SX";
        params.put("sql", sql);
        map.put("SJLX",mryeDataCenterService.searchSapList(params));
        map.remove("sql");
        return map;
    }

    /**
     * 根据类型获取商品
     *
     * @param params
     * @return
     */
    public List<Map<String, Object>> getTypeProductList(Map<String, Object> params) {
        String sql = "SELECT G3.GOODS_ID AS SPID, G3.GOODS_BH AS SPBH, G3.SHOP_ID AS DPID, G3.GOODS_NAME AS SPMC," +
                " G3.GOODS_IMG AS SPTP, G3.GOODS_PRICE AS SPDJ, G3.GOODS_WEIGHT AS SPZL, G3.GOODS_MINUS_PRICE AS SPYH," +
                " G3.GOODS_PRICE_TYPE AS SPDW, G3.GOODS_NUMBER AS SPCH, G3.GOODS_NUMBER_SALE AS SPXS, G3.GOODS_KEYWORD AS SPGJC," +
                " G3.GOODS_DES AS SPSM, G3.GOODS_CREATE_TIME AS SPTIME, G3.GOODS_ADDRESS AS SPCD, G3.GOODS_ARRAY_ID AS SPDPID,G3.GOODS_SHOW AS SPSHOW, " +
                " SJ.SJ_ID AS SJLXID, SJ.SJTYPE_NAME AS SJLXMC, SJ.SJTYPE_URL AS SJLXURL, EJ.EJ_ID AS EJLXID, EJ.EJTYPE_NAME AS EJLXMC, " +
                " EJ.EJTYPE_URL AS EJLXURL, YJ.YJ_ID AS YJLXID, YJ.YJTYPE_NAME AS YJLXMC, YJ.YJTYPE_URL AS YJLXURL FROM YST_GOODS AS G3 " +
                " LEFT JOIN YST_GOODS_SJTYPE SJ ON G3.TYPE_ID = SJ.SJ_ID AND G3.GOODS_SHOW = 1" +
                " LEFT JOIN YST_GOODS_EJTYPE EJ ON SJ.EJTYPE_ID = EJ.EJ_ID AND G3.GOODS_SHOW = 1 " +
                " LEFT JOIN YST_GOODS_YJTYPE YJ ON EJ.YJTYPE_ID = YJ.YJ_ID AND G3.GOODS_SHOW = 1 " +
                " WHERE  YJ.YJTYPE_URL = #{yjlx} OR EJ.EJTYPE_URL = #{ejlx} OR SJ.SJTYPE_URL = #{sjlx} " +
                " LIMIT #{start}, #{end}";
        params.put("sql", sql);
        return mryeDataCenterService.searchSapList(params);
    }

    /**
     * 获取商品
     *
     * @param params
     * @return
     */
    public Map<String, Object> getProductInfo(Map<String, Object> params) {
        String sql = "SELECT G1.GOODS_ID AS SPID, G1.GOODS_BH AS SPBH, G3.SHOP_ID AS DPID, G3.SHOP_NAME AS DPMC," +
                "  G1.GOODS_NAME AS SPMC, G1.GOODS_IMG AS SPTP, G1.GOODS_PRICE AS SPDJ,G1.GOODS_MINUS_PRICE AS SPYH, G1.GOODS_WEIGHT AS SPZL, " +
                "  G1.GOODS_PRICE_TYPE AS SPDW, G1.GOODS_NUMBER AS SPCH, G1.GOODS_NUMBER_SALE AS SPXS, G1.GOODS_KEYWORD AS SPGJC," +
                "  G1.GOODS_DES AS SPSM, G1.GOODS_INFO AS SPNR, G1.GOODS_CREATE_TIME AS SPTIME, G1.GOODS_ADDRESS AS SPCD," +
                "  G1.GOODS_ARRAY_ID AS SPDPID,G1.GOODS_SHOW AS SPSHOW,SJ.SJ_ID AS SJLXID, SJ.SJTYPE_NAME AS SJLXMC, SJ.SJTYPE_URL AS SJLXURL, EJ.EJ_ID AS EJLXID," +
                "  EJ.EJTYPE_NAME AS EJLXMC, EJ.EJTYPE_URL AS EJLXURL, YJ.YJ_ID AS YJLXID, YJ.YJTYPE_NAME AS YJLXMC, YJ.YJTYPE_URL AS YJLXURL FROM YST_GOODS G1 " +
                "  LEFT JOIN YST_GOODS_SJTYPE SJ ON G1.TYPE_ID = SJ.SJ_ID " +
                "  LEFT JOIN YST_GOODS_EJTYPE EJ ON SJ.EJTYPE_ID = EJ.EJ_ID " +
                "  LEFT JOIN YST_GOODS_YJTYPE YJ ON EJ.YJTYPE_ID = YJ.YJ_ID " +
                "  LEFT JOIN YST_GOODS_SHOP G3 ON G1.SHOP_ID = G3.SHOP_ID " +
                "  WHERE G1.GOODS_BH = #{SPBH}  ";
//              //非管理员
//              if(params.get("user").equals("admin")){
//                  sql=sql+"AND G1.GOODS_SHOW=1";
//              }

        params.put("sql", sql);
        return mryeDataCenterService.searchSap(params);
    }

    /**
     * 获取搭配列表
     *
     * @param params
     * @return
     */
    public List<Map<String, Object>> getProductArray(Map<String, Object> params) {
        String sql = "SELECT ARRAY_ID AS DPID, ARRAY_NAME AS DPMC, ARRAY_GOODS_ID AS SPID, ARRAY_GOODS_IMG AS DPTP, ARRAY_MONEY AS DPDJ," +
                " ARRAY_MINUS_MONEY AS DPYHDJ, ARRAY_NUMBER AS DPCH, ARRAY_NUMBER_SALE AS DPXS, ARRAY_KEYWORD AS DPGJC,ARRAY_DES AS DPSM, ARRAY_DES AS DPSM," +
                " ARRAY_CREATE_TIME AS DPSJ, ARRAY_FLAVOR AS DPKW, ARRAY_CROWD AS DPRQ, ARRAY_MENU_ID AS CPID " +
                " FROM YST_GOODS_ARRAY" +
                " WHERE 1=1 ";
        //获取指定商品搭配
        if (params.containsKey("SPID")) {
            sql = sql + " AND ARRAY_GOODS_ID LIKE concat('%',#{SPID},'%') ";
        } else {
            //根据时间获取 商品搭配列表
            sql = sql + " ORDER BY ARRAY_CREATE_TIME DESC LIMIT 0,4";
        }

        params.put("sql", sql);
        return mryeDataCenterService.searchSapList(params);
    }

    /**
     * 获取搭配商品
     *
     * @param params
     * @return
     */
    public List<Map<String, Object>> getProductArrayInfo(Map<String, Object> params) {
        String sql = "SELECT G1.GOODS_BH AS SPBH, G1.GOODS_NAME AS SPMC, G1.GOODS_DES AS SPSM,G1.GOODS_IMG AS SPTP, G1.GOODS_PRICE AS SPDJ,G1.GOODS_WEIGHT AS SPZL,G1.GOODS_PRICE_TYPE AS SPDW, " +
                "G1.GOODS_NUMBER AS SPCH, G1.GOODS_NUMBER_SALE AS SPXS, G1.GOODS_ARRAY_ID AS SPDPID,G1.GOODS_SHOW AS SPSHOW FROM YST_GOODS G1 LEFT JOIN YST_GOODS_SJTYPE G2 " +
                "ON G1.TYPE_ID = G2.SJ_ID LEFT JOIN YST_GOODS_SHOP G3 ON G1.SHOP_ID = G3.SHOP_ID " +
                "WHERE G1.GOODS_ID IN (" + params.get("SPID") + ") AND G1.GOODS_SHOW=1 ";
        params.put("sql", sql);
        return mryeDataCenterService.searchSapList(params);
    }

    /**
     * 新增商品
     *
     * @param params
     * @return
     */
    public int addadminProduct(Map<String, Object> params) {
        String sql = "INSERT INTO YST_GOODS ( `GOODS_BH`, `TYPE_ID`, `SHOP_ID`, `GOODS_NAME`, `GOODS_IMG`, `GOODS_PRICE`, `GOODS_WEIGHT`, `GOODS_MINUS_PRICE`," +
                " `GOODS_PRICE_TYPE`, `GOODS_NUMBER`, `GOODS_KEYWORD`, `GOODS_DES`, `GOODS_INFO`, `GOODS_ADDRESS`, `GOODS_ARRAY_ID`,`GOODS_SHOW` ) " +
                "  SELECT MAX(GOODS_BH) + 1,#{SPLX},#{DPID},#{SPMC},#{SPTP},#{SPDJ},#{SPZL},#{SPYH},#{SPDW},#{SPCH},#{SPGJC},#{SPSM},#{SPNR},#{SPCD},#{SPDPID},#{SPSHOW} " +
                "  FROM YST_GOODS WHERE TYPE_ID = #{SPLX}  ";
        params.put("sql", sql);

        return mryeDataCenterService.saveSap(params);
    }

    /**
     * 修改商品
     *
     * @param params
     * @return
     */
    public int updateadminProduct(Map<String, Object> params) {
        String sql = "UPDATE YST_GOODS SET `TYPE_ID` = #{SPLX}, " +
                "`SHOP_ID` = #{DPID}, " +
                "`GOODS_NAME` = #{SPMC}, " +
                "`GOODS_IMG` = #{SPTP}, " +
                "`GOODS_PRICE` = #{SPDJ}, " +
                "`GOODS_WEIGHT` = #{SPZL}, " +
                "`GOODS_MINUS_PRICE` = #{SPYH}, " +
                "`GOODS_PRICE_TYPE` = #{SPDW}, " +
                "`GOODS_NUMBER` = #{SPCH}, " +
                "`GOODS_NUMBER_SALE` = #{SPXS}, " +
                "`GOODS_KEYWORD` = #{SPGJC}, " +
                "`GOODS_DES` = #{SPSM}, " +
                "`GOODS_INFO` = #{SPNR}, " +
                "`GOODS_ADDRESS` = #{SPCD}, " +
                "`GOODS_ARRAY_ID` = #{SPDPID}, " +
                "`GOODS_SHOW`=#{SPSHOW} "+
                " WHERE GOODS_BH = #{SPBH}";
        params.put("sql", sql);

        return mryeDataCenterService.updateSap(params);
    }

    /**
     * 删除商品
     *
     * @param params
     * @return
     */
    public int daleteadminProduct(Map<String, Object> params) {
        String sql = "DELETE FROM YST_GOODS WHERE GOODS_BH IN(${SPBH})";
        params.put("sql", sql);
        return mryeDataCenterService.deleteSap(params);
    }

    //修改类别
    public int updateadminProductType(Map<String, Object> params) {
        return mryeDataCenterService.updateSap(params);
    }

    //新增类型
    public int addadminProductType(Map<String, Object> params) {
        int leixing= (int) params.get("dqlx");
        String sql="";
        if(leixing==1){
            sql="SELECT IFNULL(MAX(YJ_ID)+1,'1') yjid,IFNULL(MAX(YJTYPE_BH)+1,'521') maxbh FROM YST_GOODS_YJTYPE";
            params.put("sql",sql);
            //获取最大类型编号
            Map<String,Object> MaxBh=mryeDataCenterService.searchSap(params);
            //添加
            sql="INSERT INTO YST_GOODS_YJTYPE (`YJ_ID`, `YJID_SX`, `YJTYPE_BH`, `YJTYPE_NAME`, `YJTYPE_URL`) VALUES ("+MaxBh.get("yjid")+",#{sxid}, '"+Integer.parseInt(MaxBh.get("maxbh").toString()) +"', #{lxmc},#{lxurl});";
        }else if(leixing==2){
            sql="SELECT (select count(1) from YST_GOODS_EJTYPE)+1 AS ejid,IFNULL(MAX(EJTYPE_BH)+1,CONCAT('52',#{sjlx},'01'))  maxbh FROM YST_GOODS_EJTYPE WHERE YJTYPE_ID=#{sjlx}";
            params.put("sql",sql);
            //获取最大二级类型编号
            Map<String,Object> MaxBh=mryeDataCenterService.searchSap(params);
            //添加
            sql="INSERT INTO YST_GOODS_EJTYPE (`EJ_ID`,`EJID_SX`, `YJTYPE_ID`, `EJTYPE_BH`, `EJTYPE_NAME`, `EJTYPE_URL`) VALUES ("+MaxBh.get("ejid")+",#{sxid}, #{sjlx}, '"+Integer.parseInt(MaxBh.get("maxbh").toString())+"',#{lxmc},#{lxurl});";
        }else if(leixing==3){
            sql="SELECT EJTYPE_BH  ejbh FROM YST_GOODS_EJTYPE WHERE EJ_ID=#{sjlx}";
            params.put("sql",sql);
            //获取二级类型编号
            Map<String,Object> ejbh=mryeDataCenterService.searchSap(params);
            params.put("ejbh",ejbh.get("ejbh"));
            sql="SELECT (select count(1) from YST_GOODS_SJTYPE)+1 AS sjid,IFNULL(MAX(SJTYPE_BH)+1,CONCAT(#{ejbh},'001'))  maxbh FROM YST_GOODS_SJTYPE WHERE EJTYPE_ID=#{sjlx}";
            params.put("sql",sql);
            //获取最大三级类型编号
            Map<String,Object> MaxBh=mryeDataCenterService.searchSap(params);
            //添加
            sql="INSERT INTO YST_GOODS_SJTYPE (`SJ_ID`, `SJID_SX`, `EJTYPE_ID`, `SJTYPE_BH`, `SJTYPE_NAME`, `SJTYPE_URL`) VALUES ( "+MaxBh.get("sjid")+",#{sxid}, #{sjlx}, '"+Integer.parseInt(MaxBh.get("maxbh").toString())+"', #{lxmc}, #{lxurl});";
        }
        params.put("sql",sql);
        return mryeDataCenterService.saveSap(params);
    }

    //删除类型
    public int daleteadminProductType(Map<String, Object> params) {
        int leixing= (int) params.get("dqlx");
        String sql="";
        if(leixing==1) {
            sql="DELETE FROM YST_GOODS_YJTYPE WHERE YJ_ID=#{dqid}";

        }else if(leixing==2){
            sql="DELETE FROM YST_GOODS_SJTYPE WHERE EJTYPE_ID=#{dqid}";
            params.put("sql",sql);
            mryeDataCenterService.deleteSap(params);
            sql="DELETE FROM YST_GOODS_EJTYPE WHERE EJ_ID=#{dqid}";
        }else if(leixing==3){
            sql="DELETE FROM YST_GOODS_SJTYPE WHERE SJ_ID=#{dqid}";
        }
        params.put("sql",sql);
        return  mryeDataCenterService.deleteSap(params);
    }
}
